// 用户路由单元
var express = require('express');
var router = express.Router();
const XLSX = require('xlsx');
// 导入连接池对象
const sql = require('../db/index')

// 引入响应体
const Response = require('../utils/response')

// 新增课程评价的路由处理函数
router.post('/addReview', async (req, res) => {
  const { user_id, course_id, value1, value2, value3, value4, value5, value_average, comment, created, status, editing } = req.body;
  try {
    // 首先检查是否已经存在评价
    const checkQuery = `SELECT * FROM course_review WHERE user_id =? AND course_id =?`;
    const checkValues = [user_id, course_id];
    const [existingReview] = await sql.query(checkQuery, checkValues);
    console.log(existingReview);

    if (existingReview.length > 0) {

      // 如果已经存在评价，则返回错误响应
      return res.send(new Response(400, '您已经评价过这门课程', null));
    }
    // 执行插入语句
    const query = `INSERT INTO course_review (user_id, course_id, rating_1, rating_2, rating_3, rating_4, rating_5,rating_average, comment, created,status,editing) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)`;
    const values = [user_id, course_id, value1, value2, value3, value4, value5, value_average, comment, created, status,editing];
    await sql.query(query, values);
    // 返回成功响应
    res.send(new Response(200, '评论成功', null));
  } catch (error) {
    // 返回错误响应
    res.send(new Response(500, '评论失败', error));
  }
});



router.get('/reviewsById', async (req, res) => {
  const { courseId } = req.query;
  const query = `
    SELECT cr.id as review_id, cr.course_id, cr.rating_1, cr.rating_2, cr.rating_3, cr.rating_4, cr.rating_5, cr.comment, cr.created,cr.status,cr.tag ,bu.id as user_id, bu.userFace, bu.realname
    FROM course_review cr
    JOIN base_user bu ON cr.user_id = bu.id
    WHERE cr.course_id =?
  `;
  try {
    const [results] = await sql.query(query, [courseId]);
    res.send(new Response(200, '获取评论成功', results));
  } catch (error) {
    console.error('获取评论失败:', error);
    res.send(new Response(500, '获取评论失败', error));
  }
});

router.post('/changeStatus', async (req, res) => {
  try {
    let { id, status } = req.body;
    console.log('Received id:', id);
    console.log('Received status:', status);
    const query = 'update course_review set status=? where id =?';
    console.log('SQL query:', query, [status, id]);
    await sql.query(query, [status, id]);
    res.send(new Response(200, '状态已更新', null));
  } catch (error) {
    console.error('审核评论时出现错误:', error);
    res.send(new Response(500, '审核评论时出现错误，请稍后重试。', null));
  }
});


router.get('/reviewsByStatus2', async (req, res) => {
  const { courseId } = req.query;
  // 确保 courseId 是有效的，这里简单判断是否为空字符串
  if (!courseId) {
    return res.status(400).json(new Response(400, '缺少课程ID', null));
  }
  const query = `
SELECT cr.id as review_id, cr.course_id, cr.rating_average, cr.rating_1, cr.rating_2, cr.rating_3, cr.rating_4, cr.rating_5, cr.comment, cr.created, cr.status, cr.replyContent, cr.editing, bu.id as user_id, bu.userFace, bu.realname
FROM course_review cr
JOIN base_user bu ON cr.user_id = bu.id
WHERE cr.course_id = ?
AND cr.status = '审核通过'  -- 筛选状态为 '审核通过' 的评论  -- 筛选状态为 '审核通过' 的评论
  `;
  try {
    const [results] = await sql.query(query, [courseId]);
    res.send(new Response(200, '获取评论成功', results));
  } catch (error) {
    console.error('获取评论失败:', error);
    res.send(new Response(500, '获取评论失败', error));
  }
});


router.get('/ratings', async (req, res) => {
  const { id } = req.query;
  const query = `
    SELECT 
      AVG(rating_1) AS avg_rating_1,
      AVG(rating_2) AS avg_rating_2,
      AVG(rating_3) AS avg_rating_3,
      AVG(rating_4) AS avg_rating_4,
      AVG(rating_5) AS avg_rating_5
    FROM 
      course_review
    WHERE 
      course_id =? AND status = '审核通过'
  `;
  try {
    const [results] = await sql.query(query, [id]);
    const ratingsData = [
      { type: '互动性', avg_rating: parseFloat(results[0].avg_rating_1 || 0) },
      { type: '满意程度', avg_rating: parseFloat(results[0].avg_rating_2 || 0) },
      { type: '教师表现', avg_rating: parseFloat(results[0].avg_rating_3 || 0) },
      { type: '课程质量', avg_rating: parseFloat(results[0].avg_rating_4 || 0) },
      { type: '课程难易', avg_rating: parseFloat(results[0].avg_rating_5 || 0) }
    ];
    res.send(new Response(200, '获取成功', {
      list: ratingsData
    }))

  } catch (error) {
    res.send(new Response(500, '失败', error))
  }
});

router.get('/deleteById', async (req, res) => {
  const { id } = req.query
  if (!id) {
    return res.send(new Response(400, 'ID 未提供', null));
  }
  const deleteUserQuery = `DELETE FROM course_review WHERE id =?`;
  await sql.query(deleteUserQuery, [id]);
  res.send(new Response(200, '评论删除成功', null));
})


async function getReviewsByCourseId(course_id) {
  // 使用JOIN查询关联base_user和course_table表
  const query = `
    SELECT 
        bu.realname AS '评论者',
        ct.name AS '课程名字',
        cr.rating_average AS '综合评分',
        cr.comment AS '评论',
        cr.created AS '评论时间',
        cr.status AS '评论状态'
    FROM 
        course_review cr
    JOIN 
        base_user bu ON cr.user_id = bu.id
    JOIN 
        course_table ct ON cr.course_id = ct.id
    WHERE 
        cr.course_id = ?
  `;
  const [results] = await sql.query(query, [course_id]);
  return results;
}
// 定义路由处理函数
router.get('/exportReviews', async (req, res) => {
  // 从URL参数中获取courseId和courseName
  const { courseId, courseName } = req.query;

  // 使用 encodeURIComponent 对文件名进行编码
  const encodedFilename = encodeURIComponent(courseName).replace(/['()]/g, escape);

  try {
    // 使用courseId调用异步函数获取评价数据
    const reviews = await getReviewsByCourseId(courseId);
    console.log(reviews);

    // 将评价数据转换为Excel格式
    const worksheet = XLSX.utils.json_to_sheet(reviews);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, 'Reviews');
    const excelBuffer = XLSX.write(workbook, { type: 'buffer', bookType: 'xlsx' });
    console.log(excelBuffer, '666666666')

    // 设置HTTP响应头，告诉浏览器这是一个文件下载
    // 设置 Content-Disposition 头，使用 filename* 来指定编码后的文件名
    res.setHeader('Content-Disposition', `attachment; filename*=UTF-8''${encodedFilename}.xlsx`);
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

    // 发送Excel文件作为响应
    res.status(200).send(excelBuffer);
    // res.send(new Response(200, '导出成功', excelBuffer))
  } catch (error) {
    console.error(error);
    // res.status(500).send('导出失败');
    res.send(new Response(500, '导出失败', error))
  }
});

router.get('/reviewsByStatus', async (req, res) => {
  const { courseId } = req.query;

  if (!courseId) {
    return res.status(400).json({ status: 400, message: '缺少课程ID' });
  }

  // 查询审核通过的评论
  const reviewQuery = `
    SELECT cr.id as review_id, cr.course_id, cr.rating_average, 
           cr.rating_1, cr.rating_2, cr.rating_3, cr.rating_4, 
           cr.rating_5, cr.comment, cr.created, cr.status, cr.replyContent,cr.editing,cr.tag,
           bu.id as user_id, bu.userFace, bu.realname
    FROM course_review cr
    JOIN base_user bu ON cr.user_id = bu.id
    WHERE cr.course_id = ?
    AND cr.status = '审核通过'
  `;

  // 查询评论回复
  const replyQuery = `
    SELECT rr.id as reply_id, rr.parent_id, rr.user_id, rr.created, rr.content,
           bu.id as user_id, bu.userFace, bu.realname
    FROM review_reply rr
    JOIN base_user bu ON rr.user_id = bu.id
    WHERE rr.parent_id IN (
      SELECT id FROM course_review WHERE course_id = ? AND status = '审核通过'
    )
  `;

  try {
    const [reviewsResults] = await sql.query(reviewQuery, [courseId]);
    const [repliesResults] = await sql.query(replyQuery, [courseId]);

    // 构建 replies 数组
    const commentsWithReplies = reviewsResults.map(review => {
      // 过滤出当前评论的所有回复
      const replies = repliesResults.filter(r => r.parent_id === review.review_id)
        .map(reply => ({
          ...reply,
          // 使用回复者的 userFace 和 realname
          userFace: reply.userFace,
          realname: reply.realname
        }));

      return {
        ...review,
        replies
      };
    });

    res.json({ status: 200, message: '获取评论成功', data: commentsWithReplies });
  } catch (error) {
    console.error('获取评论失败:', error);
    res.status(500).json({ status: 500, message: '获取评论失败', error: error.message });
  }
});


router.post('/addReply', (req, res) => {
  const { parent_id, user_id, content } = req.body;
  const created = new Date(); // 使用当前时间戳作为创建时间

  // 准备 SQL 插入语句
  const insertSql = 'INSERT INTO review_reply (parent_id, user_id, created, content) VALUES (?, ?, ?, ?)';

  // 使用连接池执行 SQL 语句，并通过 .then() 和 .catch() 处理结果和错误
  sql.query(insertSql, [parent_id, user_id, created.toISOString(), content])
    .then(([results]) => {
      // 发送成功响应
      res.send(new Response(200,'回复成功',null))
    })
    .catch(error => {
      res.send(new Response(500,'回复失败',error))

    });
});


module.exports = router;
